package sql;

public class Sql {


//	-- 1
//	select student.*,t3.score1,t3.score2 from student
//							inner join(select t1.s_id,t1.score score1,t2.score score2 from(select * from sc where c_id = '1') t1
//										join (select * from sc where c_id = '2') t2
//	                                    on t1.s_id=t2.s_id ) t3
//	                            on t3.s_id=student.s_id where t3.score1> t3.score2;
//	                            
//	select student.*,t3.score1,t3.score2 from student
//							inner join(select t1.s_id,t1.score score1,t2.score score2 from(select * from sc where c_id = '1') t1
//										left join (select * from sc where c_id ='2') t2
//	                                    on t1.s_id=t2.s_id) t3
//	                            on t3.s_id=student.s_id  where (t3.score1>t3.score2 or t3.score2 is null);
//	                
//	select student.*,t3.score1,t3.score2 from student
//							inner join(select t1.s_id,t1.score score1,t2.score score2 from(select * from sc where c_id = '1') t1
//										right join (select * from sc where c_id = '2') t2
//	                                    on t1.s_id=t2.s_id ) t3
//	                            on t3.s_id=student.s_id where (t3.score1>t3.score2 or t3.score1 is null);	                
//								
//	-- 2 
//	select student.s_id,student.s_name,sc1.avgg from student
//						right join(select s_id,avg(score) avgg from sc group by sc.s_id having avg(score)>=60) sc1
//	                    on student.s_id=sc1.s_id;
//	 
//	-- 3
//	select * from student where student.s_id in (select s_id from sc group by sc.s_id);
//
//	-- 4
//	select 	student.*,sc1.sum1,sc1.sum2 from student 
//				left join(select sc.s_id,sum(score) sum1,sum(1) sum2 from sc group by s_id) sc1 
//						on student.s_id=sc1.s_id;
//	select student.*,sc1.sum1,sc1.sum2 from student 
//				inner join(select sc.s_id,sum(score) sum1,sum(1) sum2 from sc group by s_id) sc1 
//						on student.s_id=sc1.s_id;
//	-- 5
//	select sum(1) nums from (select * from teacher where teacher.t_name like '李%') t1;
//
//	-- 6
//	select student.* from student 
//			 join (select * from sc 
//				 where sc.c_id in(select course.c_id from course where course.t_id = 
//						(select teacher.t_id from teacher where teacher.t_name='张三'))
//							) sc1 on student.s_id=sc1.s_id;
//
//	-- 7
//	select student.* from student left join (select sc.s_id,sum(1) num from sc group by s_id) sc1
//						on student.s_id=sc1.s_id where sc1.num<(select sum(1) from course) or sc1.num is null;
//	 
//	-- 8
//	select student.* from student where student.s_id in(select sc.s_id from sc where sc.c_id in(select sc.c_id from sc where sc.s_id=1));
//
//	-- 9
//	select student.* from student where s_id in
//	(select s_id from sc where c_id in(
//		select c_id from sc where s_id ='01')
//	and s_id not in(select s_id from sc where c_id not in(select c_id from sc where s_id ='01'))
//	and s_id <> '01'
//	group by s_id having count(c_id)=(select count(c_id) from sc where s_id=1));
//
//
//	-- 10
//	select student.s_name from student left join (select s_id from(select * from sc order by c_id ) a join
//	(select course.c_id from course where t_id =
//	(select teacher.t_id from teacher where teacher.t_name='张三')) b
//	on a.c_id=b.c_id) sc on student.s_id=sc.s_id where sc.s_id is null;
//
//	-- 11
//	select student.s_id,student.s_name,avgg from student join
//	(select s_id,count(1) c,avg(score) avgg from sc where sc.score<60 group by s_id having c>=2) sc1
//	on student.s_id=sc1.s_id;
//
//
//	-- 12
//	select student.* from student join(select * from sc where sc.c_id=1 and score<=60 ) sc1 on student.s_id=sc1.s_id order by sc1.score desc;
//
//
//	-- 18
//	select * from sc a where (select count(1) from sc where c_id =a.c_id and score >a.score)<3 order by a.c_id,c_id;
//	-- sc中大于a的人的个数小于3，则他为前三
//
//	-- 19
//	select course.*,c from course join(select c_id,count(1) c from sc group by sc.c_id) sc1 on course.c_id=sc1.c_id;
//
//
//	-- 20
//	select student.* from student where s_id in(select s_id from(
//	select s_id,count(c_id) c from sc group by s_id) sc1 where c=2);
//
//	-- 21
//	select count(1) from student group by s_sex;
//
//	-- 22
//	select * from student where s_name like('%风%');
//
//	-- 23
//	select s1.*,count(1) from student s1 join student s2 on s1.s_name=s2.s_name and s1.s_sex=s2.s_sex;
//
//	-- 24
//	select * from student where year(s_age) = 1990;
//
//	-- 25
//	select sc.c_id,avg(sc.score) '平均分' from sc group by sc.c_id;
//
//	-- 26 查询平均成绩大于等于 85 的所有学生的学号、姓名和平均成绩
//	select * from student a
//	left join (
//	select s_id,avg(score) avgs
//	from sc 
//	group by s_id) b on a.s_id=b.s_id where b.avgs>=85;
//
//	-- 27
//	select s.s_name,m.score from student s join (select sc.s_id,sc.score from sc where sc.c_id=(select c.c_id from course c where c.c_name='数学') and sc.score<60) m on s.s_id=m.s_id;
//
//	-- 28
//	create view s_c1 as
//	select s.s_id,s.s_name,c1.score '课程1' from student s left join course11 c1 on s.s_id=c1.s_id;
//	create view s_c2 as
//	select s.*,c2.score'课程2' from s_c1 s left join course22 c2 on s.s_id=c2.s_id;
//	create view s_c as
//	select s.*,c3.score'课程3' from s_c2 s left join course33 c3 on s.s_id=c3.s_id;
//
//	select * from student a left join sc b on a.s_id=b.s_id;
//
//	-- 29
//	select *from (
//	select b.*,c.c_name from (select c_id from sc where score >70)b
//	left join course c on c.c_id=b.c_id) d left join student a on d.s_id=a.s_id;
//
//	-- 30
//	select * from sc where score <60;
//
//	-- 31
//	select s_id,s_name from student where s_id in (select s_id from sc where c_id='01'and score>=80);
//
//	-- 32
//	select c_id,count(1) '学生人数' from sc group by c_id;
//
//	-- 33
//	select s_id,max(score) from sc where c_id=(select c.c_id from course c where c.t_id=(select t_id from teacher where t_name='张三'));
//
//	-- 34
//	select s.*,zz.sc0ore from student s join (select s_id,score from sc where score=(select max(score) from sc where c_id=(select c.c_id from course c where c.t_id=(select t_id from teacher where t_name='张三')))) zz on s.s_id=zz.s_id;
//
//	-- 37
//	select c_id,count(s_id) cs
//	from sc
//
//	group by c_id having count(s_id)>5 order by cs desc,c_id;
//
//	-- 38 
//	select s_id from sc group by s_id having count(c_id)>=2;
//
//	-- 39
//	select s_id
//	from sc group by s_id having count(c_id)=(select distinct count(1) from course);
//
//
//	select * from student;
//
//	select * from course;
//
//	select * from teacher;
//
//	select * from sc;
}
